<?php
class Application_Model_Content extends Application_Model_Db_Db
{
    protected $_feedCategoryType = 2;
    /**
     * get all category and department content
     * @param $categoryId
     * @param int $departmentId
     * @return array
     */
    public function getContentByCategoryId($categoryId, $departmentId=0, $count=1) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`preview_mediadata`,
                    `content`.`preview_text`,
                    `content`.`description`,
                    `content`.`keywords`,
                    `content`.`title`,
                    `content`.`text`,
                    `content`.`alias`,
                    DATE_FORMAT(`content`.`date_create`, '%d %b %Y %h:%i %p') AS `date_create`,
                    `content`.`visits`,
                    `category`.`id` AS `category_id`,
                    `category`.`category_name`
                FROM `content`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                WHERE 
                    1
                    AND `category`.`id` = ?
                    AND `category`.`department_id` = ?
                    AND `content`.`is_visible` = 1                
                ORDER BY `content`.`date_create` DESC
                LIMIT {$count}";
        return $this->_db->fetchAll($sql, array($categoryId, $departmentId));
    }

    public function getAllContent() {
        $sql = "SELECT `id`, `text`, `preview_text` FROM `content`";
        return $this->_db->fetchAll($sql);
    }

    public function updateContent($id, $data) {
        $this->_db->update('content', $data, 'id=' . $id);
    }

    public function autoloadContentForFeed($lastContentId) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`preview_mediadata`,
                    `content`.`preview_text`,
                    `content`.`title`,                    
                    `content`.`alias`,
                    DATE_FORMAT(`content`.`date_create`, '%d %b %Y %h:%i %p') AS `date_create`                    
                FROM `content`
                INNER JOIN `content` AS `last_content`
                    ON `content`.`category_id` = `last_content`.`category_id`
                    AND `content`.`date_create` < `last_content`.`date_create`
                WHERE 
                    1
                    AND `last_content`.`id` = ?                    
                    AND `content`.`is_visible` = 1                
                ORDER BY `content`.`date_create` DESC
                LIMIT 1";
        return $this->_db->fetchAll($sql, $lastContentId);
    }

    public function makePreviewForFeed($content) {
        foreach ($content as &$post) {            
            if (!preg_match('/iframe|object/', $post['preview_mediadata'])
                && $post['preview_mediadata']) {
                $image = $post['preview_mediadata'];
                $post['preview_mediadata'] = '<img 
                                               src="/uploads/preview_images/'.$image.'" 
                                               alt="photo">
                                              </img>';
            }                    
            $post['tags'] = $this->getPostTags($post['id']); 
        }
        return $content;
    }

    /**
     * get most visits post.Default = 5
     * @param int $countPosts
     * @return array
     */
    public function getPopularPosts($countPosts=5) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`title`,
                    `content`.`alias`,
                    DATE_FORMAT(`content`.`date_create`, '%d %M %Y') AS `date_create`
                FROM `content`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                WHERE `category`.`type_id` = {$this->_feedCategoryType}
                ORDER BY `visits` DESC
                LIMIT 0, {$countPosts}";
        return $this->_db->fetchAll($sql);
    }

    /**
     * get post for static page by his alias.
     * Static pages may have only one post
     * @param $alias
     * @return array
     */
    public function getPostByCategoryAlias($alias, $departmentId=0) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`title`,
                    `content`.`text`
                FROM `content`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id` 
                WHERE 
                    1
                    AND `category`.`alias` = ?
                    AND `category`.`department_id` = ?";          
        return $this->_db->fetchRow($sql, array($alias, $departmentId));
    }

    /**
     * get post by alias
     * @param $alias
     * @return array
     */
    public function getPostByAlias($alias) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`title`,
                    `content`.`preview_mediadata`,
                    `content`.`preview_text`,
                    `content`.`text`
                FROM `content`
                WHERE `content`.`alias` = ?";
        return $this->_db->fetchRow($sql, $alias);
    }

    /**
     * get all post tags
     * @param $postId
     * @return array
     */
    public function getPostTags($postId) {
        $sql = "SELECT
                   `tag`.`id`,
                   `tag`.`tag_name`
                FROM `tag`
                INNER JOIN `content_tag`
                    ON `tag`.`id` = `content_tag`.`tag_id`
                WHERE `content_tag`.`content_id` = ?";
        return $this->_db->fetchAll($sql, $postId);
    }

    public function visitContent($contentId) {
        $sql = "UPDATE 
                    `content`
                SET `visits` = `visits` + 1
                WHERE `content`.`id` = {$contentId}";
        $this->_db->query($sql);
    }

    public function getContentByTagId($tagId) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`description`,
                    `content`.`keywords`,
                    `content`.`title`,
                    `content`.`preview_mediadata`,
                    `content`.`preview_text`,
                    `content`.`text`,
                    `content`.`alias`,
                    DATE_FORMAT(`content`.`date_create`, '%d %b %Y %h:%i %p') AS `date_create`,
                    `content`.`visits`,
                    `category`.`id` AS `category_id`,
                    `category`.`category_name`
                FROM `content`                
                INNER JOIN `content_tag`
                    ON `content`.`id` = `content_tag`.`content_id`
                INNER JOIN `tag`
                    ON `content_tag`.`tag_id` = `tag`.`id`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                WHERE `tag`.`id` = ?
                ORDER BY `content`.`date_create` DESC";
        return $this->_db->fetchAll($sql, $tagId);
    }
}